<?php
/**
 * @copyright : ienter asia Technologies
 * @author :  ThanhNV
 * @version    1.0
 * @Description : Export salary from database to Excel 
 */
if (!defined('BASEPATH')) exit('No direct script access allowed');

class Mod_payroll extends MYAD_Controller {
    /**
     * function __construct
     * not overloading parent __construct
     */    
    function __construct() {
        parent::__construct();
        $this->load->model('Mmod_payroll','Payroll');   
        $this->load->model('mod_listtimecard/Mmod_listtimecard','Listtimecard');   
        $this->load->library('pagination'); 
        $this->load->helper('date');
        date_default_timezone_set("Asia/Ho_Chi_Minh");  
        $this->load->helper('file');
        delete_files(FCPATH.'/temp/excel/company/');
              
    }
    /**
     * function index
     * show data list salary of employee
     */
    function index($offset = NULL) {              
        $employeeName=NULL;
         $data["employeeName"]="";         
        if($this->input->post('btnSubmit')){
                $employeeName= $this->input->post('txtEmployeeName');    
                $data["employeeName"] =$employeeName;       
        }      
        
        if(!is_null($offset))
                 {
                     $offset = $this->uri->segment(2);
                 }
                 
        $limit = 10;                 
        $config['uri_segment'] = 2;
        $config['base_url'] = base_url('employeesalary'); 
        $config['total_rows'] =$this->Payroll->countAll($employeeName);        
        $config['per_page'] = $limit;
        $config['num_links'] = 5;
        $config['first_link'] = $this->lang->line('pagi_first');
        $config['last_link'] =  $this->lang->line('pagi_last');;
        $config['prev_link'] = $this->lang->line('pagi_prev');
        $config['cur_tag_open'] = '<a class="h_pagination_active">';
        $config['next_link'] = $this->lang->line('pagi_next');
        $this->pagination->initialize($config);
        $data['title'] = $this->lang->line('esl__employeesalarylist');        
        $data['data']=$this->Payroll->getEmployeeSearch($limit,$offset,$employeeName); 
        $data['messsageError']=(count($data['data'])==0) ? $this->lang->line('MC003') : "";
        $data['totalSalary']=$this->Payroll->getTotalSalaryOnMonth($employeeName);
        $data['js'] = array('jquery-ui.js','opensave.js');
        $data['css'] = array('jquery-ui.css');
        $data['show'] = "employeesalarylist";    
        $data['page']=$offset;
        $this->load->view('layout', $data);
    }
        /**
     * @author Thanh NV        
     * @function exportEmployeeID 
     * @param int $bat a number to be multiplied 
     * @param int $baz a number to be multiplied 
     * @return int the product of the two parameters 
     * @This function export data to database to excel  each employee of company  
     */ 
        function exportEmployeeID(){            
        
            $employeeId = $this->uri->segment(3);
            $month = $this->uri->segment(4);
            $year = $this->uri->segment(5);            
            $newCurrency= $this->Listtimecard->getCurrencyConversionRate();     
            $newPITParameter= $this->Listtimecard->getPITParameter();  
            $newSalaryParameter= $this->Listtimecard->getSalaryParameter();
            $newPersonalIncomeTax= $this->Listtimecard->getPersonalIncomeTax();
            
            //caculating               
            $data=$this->Payroll->getEmployeeID($employeeId,$month,$year);
            $month= $data[0]['Month'].'/'. $data[0]['Year'];           
            
            $E25=($data[0]['BasicSalary']/$data[0]['ActualWorkingHours'])*$data[0]['ActualAttendant'];
            $E24=$E25+$data[0]['OvertimeAllowance']+$data[0]['Bonus'];
            $E29=$data[0]['SocialInsurance']+ $data[0]['MedicalInsurance']+$data[0]['UnemploymentInsurance'];
            $E28=($data[0]['BasicSalary']>=23000000)? 23000000:$data[0]['BasicSalary'];           
            $monthName = date('F', mktime(0, 0, 0, $data[0]['Month'], 10));
            $num = cal_days_in_month(CAL_GREGORIAN,  $data[0]['Month'], $data[0]['Year']);         
            $D2="For the period from 1st ".$monthName." ".$data[0]['Year']." to ".$num."th ".$monthName." ".$data[0]['Year'];
            $D33="(N)=".number_format($newPITParameter->EmployeeDeductionAmount,0, '.', ',')."+{(C)×".number_format($newPITParameter->DeductionPerDependant,0, '.', ',')."}";
            $D26="(J)= (E2)*". $newSalaryParameter->WeekdayOvertimeRate*100 ."%+(E3)*". $newSalaryParameter->WeekdayLateOvertimeRate*100 ."%  +(F1)*". $newSalaryParameter->WeekendOvertimeRate*100 ."%+(F2)*". $newSalaryParameter->WeekendLateOvertimeRate*100 ."%+(G1)*". $newSalaryParameter->HolidayOvertimeRate*100 ."%+(G2)*". $newSalaryParameter->HolidayLateOvertimeRate*100 ."%";
            $D30="(M1)=(L1)*". $newSalaryParameter->SocialInsuranceRate*100 ."%";
            $D31="(M2)=(L1)*". $newSalaryParameter->MedicalInsuranceRate*100 ."%";
            $D32="(M3)=(L1)*". $newSalaryParameter->UnemploymentInsuranceRate*100 ."%";
            $B30="Social Insurance
        (Employee): (". $newSalaryParameter->SocialInsuranceRate*100 ."%)";
            $B31="Medical Insurance
        (Employee): (". $newSalaryParameter->MedicalInsuranceRate*100 ."%)";
            $B32="Unemployment Insurance
        (Employee): (". $newSalaryParameter->UnemploymentInsuranceRate*100 ."%)";
            $C30="社会保険（従業員）：（". $newSalaryParameter->SocialInsuranceRate*100 ."％）";
            $C31="医療保険（従業員）：（". $newSalaryParameter->MedicalInsuranceRate*100 ."％）";
            $C32="失業保険（従業員）：（". $newSalaryParameter->UnemploymentInsuranceRate*100 ."％）";
            $this->load->library("excel");            
            $this->excel->load(APPPATH.'exports/excel/template/template.xls');       
            $this->excel->setActiveSheetIndex(0);             
            $this->excel->getActiveSheet()->setTitle(subString($this->replaceSpecial($data[0]['EmployeeName']),31,null));
            //
            //set cell content with some text
            $this->excel->getActiveSheet()->SetCellValue('D2', $D2);
            $this->excel->getActiveSheet()->SetCellValue('D26', $D26);
            $this->excel->getActiveSheet()->SetCellValue('D30', $D30);
            $this->excel->getActiveSheet()->SetCellValue('D31', $D31);
            $this->excel->getActiveSheet()->SetCellValue('D32', $D32);            
            $this->formatExcel();
            $this->excel->getActiveSheet()->SetCellValue('B3', $data[0]['EmployeeName']);
            $this->excel->getActiveSheet()->SetCellValue('B30', $B30);
            $this->excel->getActiveSheet()->SetCellValue('B31', $B31);
            $this->excel->getActiveSheet()->SetCellValue('B32', $B32);
            
            $this->excel->getActiveSheet()->SetCellValue('C30', $C30);
            $this->excel->getActiveSheet()->SetCellValue('C31', $C31);
            $this->excel->getActiveSheet()->SetCellValue('C32', $C32);
            
            $this->excel->getActiveSheet()->SetCellValue('E3', 'I ENTER '.$data[0]['EmployeeId']);
            $this->excel->getActiveSheet()->SetCellValue('E4', $data[0]['PositionName']);
            $this->excel->getActiveSheet()->SetCellValue('E5', $newCurrency->Rate);
            $this->excel->getActiveSheet()->SetCellValue('E9', $this->numberToHours($data[0]['ActualWorkingHours']));
            $this->excel->getActiveSheet()->SetCellValue('E10', $data[0]['BasicSalary']);
            $this->excel->getActiveSheet()->SetCellValue('E11', $data[0]['BasicSalaryPerHour']);
            $this->excel->getActiveSheet()->SetCellValue('E12', $data[0]['Dependants']);
            $this->excel->getActiveSheet()->SetCellValue('E13', $data[0]['PaidLeave']);
            $this->excel->getActiveSheet()->SetCellValue('E14', $data[0]['SpecialLeave']);
            $this->excel->getActiveSheet()->SetCellValue('E15', $data[0]['UnpaidLeave']);
            $this->excel->getActiveSheet()->SetCellValue('E16', $this->numberToHours($data[0]['ActualAttendant']));
            $this->excel->getActiveSheet()->SetCellValue('E17', $this->numberToHours($data[0]['WeekdayOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E18', $this->numberToHours($data[0]['WeekdayLateOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E19', $this->numberToHours($data[0]['WeekendOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E20', $this->numberToHours($data[0]['WeekendLateOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E21', $this->numberToHours($data[0]['HolidayOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E22', $this->numberToHours($data[0]['HolidayLateOvertime']));
            $this->excel->getActiveSheet()->SetCellValue('E23', $this->numberToHours($data[0]['LateArrivalTime']));
            $this->excel->getActiveSheet()->SetCellValue('E24', $E24);
            $this->excel->getActiveSheet()->SetCellValue('E25', $E25);
            $this->excel->getActiveSheet()->SetCellValue('E26', $data[0]['OvertimeAllowance']);
            $this->excel->getActiveSheet()->SetCellValue('E27', $data[0]['Bonus']);
            $this->excel->getActiveSheet()->SetCellValue('E28', $E28);
            $this->excel->getActiveSheet()->SetCellValue('E29', $E29);
            $this->excel->getActiveSheet()->SetCellValue('E30', $data[0]['SocialInsurance']);
            $this->excel->getActiveSheet()->SetCellValue('E31', $data[0]['MedicalInsurance']);
            $this->excel->getActiveSheet()->SetCellValue('E32', $data[0]['UnemploymentInsurance']);
            $this->excel->getActiveSheet()->SetCellValue('E33', $data[0]['DeductionForDependants']);
            $this->excel->getActiveSheet()->SetCellValue('E34', $data[0]['IncomeForPITCalculation']);
            $this->excel->getActiveSheet()->SetCellValue('E35', $data[0]['PersonalIncomeTax']);
            $this->excel->getActiveSheet()->SetCellValue('E36', $data[0]['NetSalary']);          
            $filename=date("Y-m-d").'-'.date('H',time()).date('i').'-'.subString($this->replaceSpecial($data[0]['EmployeeName']),60,null).'.xls';                        
            $this->excel->save(FCPATH.'temp/excel/company/'.$filename);             
            header('Content-Type: application/vnd.ms-excel');            
            header('Content-Disposition: attachment;filename="'.$filename);
            header('Cache-Control: max-age=0');
            $this->excel->save('php://output');
            redirect('/employeesalary');    
    }
    /**
     * function exportEmployeeID
     * function export data to database to excel  all employee of company
     */
    function exportEmployee() {                     
           ob_start();
           $name = $this->uri->segment(3); 
            if(!empty($name)){
                $arrEmployeeSearch=$this->Payroll->getEmployeeSearch(NULL,NULL,$name);                        
            }else{                
                $arrEmployeeSearch=$this->Payroll->getEmployeeSearch(NULL,NULL,NULL); 
            }    
           
            $this->load->library("excel");        
            
            $this->excel->load(APPPATH.'exports/excel/template/template.xls');       
            
            $i=0;
            foreach($arrEmployeeSearch as $row){              
                
                   
                $data=$this->Payroll->getEmployeeID($row['EmployeeId'],NULL,NULL);                           
                $this->excel->setActiveSheetIndex($i);                 
                $this->excel->getActiveSheet()->setTitle(subString($this->replaceSpecial($data[0]['EmployeeName']),31,null));
                //       
                $newCurrency= $this->Listtimecard->getCurrencyConversionRate();     
                $newPITParameter= $this->Listtimecard->getPITParameter();  
                $newSalaryParameter= $this->Listtimecard->getSalaryParameter();
                $newPersonalIncomeTax= $this->Listtimecard->getPersonalIncomeTax();                                  
                $month= $data[0]['Month'].'/'. $data[0]['Year'];
                $E25=($data[0]['BasicSalary']/$data[0]['ActualWorkingHours'])*$data[0]['ActualAttendant'];
                $E24=$E25+$data[0]['OvertimeAllowance']+$data[0]['Bonus'];
                $E29=$data[0]['SocialInsurance']+ $data[0]['MedicalInsurance']+$data[0]['UnemploymentInsurance'];
                $E28=($data[0]['BasicSalary']>=23000000)? 23000000:$data[0]['BasicSalary'];           
                $monthName = date('F', mktime(0, 0, 0, $data[0]['Month'], 10));
                $num = cal_days_in_month(CAL_GREGORIAN,  $data[0]['Month'], $data[0]['Year']);         
                $D2="For the period from 1st ".$monthName." ".$data[0]['Year']." to ".$num."th ".$monthName." ".$data[0]['Year'];
                $D33="(N)=".number_format($newPITParameter->EmployeeDeductionAmount,0, '.', ',')."+{(C)×".number_format($newPITParameter->DeductionPerDependant,0, '.', ',')."}";
                $D26="(J)= (E2)*". $newSalaryParameter->WeekdayOvertimeRate*100 ."%+(E3)*". $newSalaryParameter->WeekdayLateOvertimeRate*100 ."%  +(F1)*". $newSalaryParameter->WeekendOvertimeRate*100 ."%+(F2)*". $newSalaryParameter->WeekendLateOvertimeRate*100 ."%+(G1)*". $newSalaryParameter->HolidayOvertimeRate*100 ."%+(G2)*". $newSalaryParameter->HolidayLateOvertimeRate*100 ."%";
                $D30="(M1)=(L1)*". $newSalaryParameter->SocialInsuranceRate*100 ."%";
                $D31="(M2)=(L1)*". $newSalaryParameter->MedicalInsuranceRate*100 ."%";
                $D32="(M3)=(L1)*". $newSalaryParameter->UnemploymentInsuranceRate*100 ."%";
                $B30="Social Insurance
            (Employee): (". $newSalaryParameter->SocialInsuranceRate*100 ."%)";
                $B31="Medical Insurance
            (Employee): (". $newSalaryParameter->MedicalInsuranceRate*100 ."%)";
                $B32="Unemployment Insurance
            (Employee): (". $newSalaryParameter->UnemploymentInsuranceRate*100 ."%)";
                $C30="社会保険（従業員）：（". $newSalaryParameter->SocialInsuranceRate*100 ."％）";
                $C31="医療保険（従業員）：（". $newSalaryParameter->MedicalInsuranceRate*100 ."％）";
                $C32="失業保険（従業員）：（". $newSalaryParameter->UnemploymentInsuranceRate*100 ."％）";
                //set cell content with some text
                $this->excel->getActiveSheet()->SetCellValue('D2', $D2);
                $this->excel->getActiveSheet()->SetCellValue('D26', $D26);
                $this->excel->getActiveSheet()->SetCellValue('D30', $D30);
                $this->excel->getActiveSheet()->SetCellValue('D31', $D31);
                $this->excel->getActiveSheet()->SetCellValue('D32', $D32);
                $this->formatExcel();
               
                $this->excel->getActiveSheet()->SetCellValue('B3', $data[0]['EmployeeName']);
                $this->excel->getActiveSheet()->SetCellValue('B30', $B30);
                $this->excel->getActiveSheet()->SetCellValue('B31', $B31);
                $this->excel->getActiveSheet()->SetCellValue('B32', $B32);

                $this->excel->getActiveSheet()->SetCellValue('C30', $C30);
                $this->excel->getActiveSheet()->SetCellValue('C31', $C31);
                $this->excel->getActiveSheet()->SetCellValue('C32', $C32);

                $this->excel->getActiveSheet()->SetCellValue('E3', 'I ENTER '.$data[0]['EmployeeId']);
                $this->excel->getActiveSheet()->SetCellValue('E4', $data[0]['PositionName']);
                $this->excel->getActiveSheet()->SetCellValue('E5', $newCurrency->Rate);                
                $this->excel->getActiveSheet()->SetCellValue('E9', $this->numberToHours($data[0]['ActualWorkingHours']));                 
                $this->excel->getActiveSheet()->SetCellValue('E10', $data[0]['BasicSalary']);
                $this->excel->getActiveSheet()->SetCellValue('E11', $data[0]['BasicSalaryPerHour']);
                $this->excel->getActiveSheet()->SetCellValue('E12', $data[0]['Dependants']);
                $this->excel->getActiveSheet()->SetCellValue('E13', $data[0]['PaidLeave']);
                $this->excel->getActiveSheet()->SetCellValue('E14', $data[0]['SpecialLeave']);
                $this->excel->getActiveSheet()->SetCellValue('E15', $data[0]['UnpaidLeave']);
                $this->excel->getActiveSheet()->SetCellValue('E16', $this->numberToHours($data[0]['ActualAttendant']));
                $this->excel->getActiveSheet()->SetCellValue('E17', $this->numberToHours($data[0]['WeekdayOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E18', $this->numberToHours($data[0]['WeekdayLateOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E19', $this->numberToHours($data[0]['WeekendOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E20', $this->numberToHours($data[0]['WeekendLateOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E21', $this->numberToHours($data[0]['HolidayOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E22', $this->numberToHours($data[0]['HolidayLateOvertime']));
                $this->excel->getActiveSheet()->SetCellValue('E23', $this->numberToHours($data[0]['LateArrivalTime']));
                $this->excel->getActiveSheet()->SetCellValue('E24', $E24);
                $this->excel->getActiveSheet()->SetCellValue('E25', $E25);
                $this->excel->getActiveSheet()->SetCellValue('E26', $data[0]['OvertimeAllowance']);
                $this->excel->getActiveSheet()->SetCellValue('E27', $data[0]['Bonus']);
                $this->excel->getActiveSheet()->SetCellValue('E28', $E28);
                $this->excel->getActiveSheet()->SetCellValue('E29', $E29);
                $this->excel->getActiveSheet()->SetCellValue('E30', $data[0]['SocialInsurance']);
                $this->excel->getActiveSheet()->SetCellValue('E31', $data[0]['MedicalInsurance']);
                $this->excel->getActiveSheet()->SetCellValue('E32', $data[0]['UnemploymentInsurance']);
                $this->excel->getActiveSheet()->SetCellValue('E33', $data[0]['DeductionForDependants']);
                $this->excel->getActiveSheet()->SetCellValue('E34', $data[0]['IncomeForPITCalculation']);
                $this->excel->getActiveSheet()->SetCellValue('E35', $data[0]['PersonalIncomeTax']);
                $this->excel->getActiveSheet()->SetCellValue('E36', $data[0]['NetSalary']);            
                $filename='EmployeeSalary.xls';          
                          
                $i++;                
               
                 $this->excel->cloneSheet(); 
         
            }             
            header('Content-Type: application/vnd.ms-excel');            
            header('Content-Disposition: attachment;filename="'.$filename);
            header('Cache-Control: max-age=0');
            $this->excel->save('php://output');
            
           // $this->excel->save(FCPATH.'temp/excel/company/'.$filename);      
            
//            redirect(base_url().'temp/excel/company/'.$filename); 
//            redirect('/employeesalary');           
        }
        
        /**
            * @author Thanh NV        
            * @created:2014/06/23
            * @function numberToHours 
            * @param: int           
            * @Output: string
            * @This function change number to minutes  
            */ 
        function numberToHours($number=NUL) {              
            $strHours=NULL; $arrNumber=array();
            $arrNumber=explode('.',$number);                           
            if(isset($arrNumber[1])>1){
                $strHours=$arrNumber[0].':'.$arrNumber[1]*6;
            }elseif(isset($arrNumber[1])==1){
                $strHours=$arrNumber[0].':0'.$arrNumber[1]*6;
            }elseif($arrNumber[0]!=0){
                $strHours=$arrNumber[0].':00';                
            }else{    
                $strHours=0;                
            }            
            return $strHours;            
        }
        
        /**
            * @author Thanh NV        
            * @created:2014/07/02
            * @function formatExcel 
            * @This function format cell in excel 
            */ 
           function formatExcel() {
               
                $this->excel->getActiveSheet()
                            ->getStyle('E3')
                            ->getAlignment()
                            ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                $this->excel->getActiveSheet()
                        ->getStyle('E9')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->excel->getActiveSheet()
                        ->getStyle('E16')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->excel->getActiveSheet()
                        ->getStyle('E17')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->excel->getActiveSheet()
                        ->getStyle('E18')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
                $this->excel->getActiveSheet()
                        ->getStyle('E19')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->excel->getActiveSheet()
                        ->getStyle('E20')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $this->excel->getActiveSheet()
                        ->getStyle('E21')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
               $this->excel->getActiveSheet()
                        ->getStyle('E22')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT); 
               $this->excel->getActiveSheet()
                        ->getStyle('E23')
                        ->getAlignment()
                        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
           }
           
           /**
        * @author Thanh NV        
        * @created:2014/06/23
        * @function replaceSpecial 
        * @param: $strString special          
        * @Output: $strString
        * @This function return $strString no special character
        */ 
           function replaceSpecial($strString) {
               $order   = array('<', '>', '?','[',']',':','|','*','/','!','|','"');
               $replace = '';
               return str_replace($order, $replace, stripslashes($strString));
           }
           
  
    
}  